using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class StaffFavorite : IStaffFavorite
    {
        // Methods
        public void DeleteStaffFavorite(int id)
        {
            string cmdText = "UPDATE Staff_Favorite SET IsDel='1' WHERE Favorite_ID = " + id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public DataTable Favorite(int id)
        {
            string cmdText = "select s.Title,s.Url,m.MENU_DISPLAY_NAME from Staff_Favorite s,SYS_MENU m where s.Module_ID=m.MENU_UID and s.IsDel='0' and s.Staff_ID = " + id;
            SqlParameter[] parameterArray = new SqlParameter[0];
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }

        public IList GetStaffFavoriteList(int iStaff_ID)
        {
            string cmdText = "select Favorite_ID,Staff_ID,Title,Url,Create_Date,Favorite_Type,Module_ID,IsDel from Staff_Favorite where Staff_ID = " + iStaff_ID;
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    StaffFavoriteInfo info = new StaffFavoriteInfo();
                    info.Favorite_ID = reader.GetInt32(0);
                    info.Staff_ID = reader.GetInt32(1);
                    info.Title = reader.GetString(2);
                    info.Url = reader.GetString(3);
                    info.Favorite_Type = reader.GetByte(5);
                    info.Module_ID = reader.GetInt32(6);
                    list.Add(info);
                }
            }
            return list;
        }

        public void InsertStaffFavorite(StaffFavoriteInfo info)
        {
            string cmdText = string.Concat(new object[] { "insert into Staff_Favorite( Staff_ID,Title,Url,Favorite_Type,Module_ID,IsDel ) values ( ", info.Staff_ID, ",'", info.Title, "','", info.Url, "',", info.Favorite_Type, ",", info.Module_ID, "0 )" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}